﻿using Common.AspNetCore;
using Common.Infrastructure;
using Common.RunModels.File;
using File.Api.Models;
using File.Api.Repository;
using File.Api.Service;
using File.Api.Utils;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using static Common.Enums.File.FileEnum;

namespace File.Api.Controllers
{
    [Route("api/Excel")]
    public class ExcelController : ApiController
    {
        private readonly HeyiExcelService heyi;
        private readonly PdfService pdfService;
        private readonly FileRepository fileRepository;
        private readonly IConfiguration configuration;

        public ExcelController(IServiceProvider provider, HeyiExcelService heyi, PdfService pdfService, FileRepository fileRepository, IConfiguration configuration)
             : base(provider)
        {
            this.heyi = heyi;
            this.pdfService = pdfService;
            this.fileRepository = fileRepository;
            this.configuration = configuration;
        }

        [HttpPost, Route("GetExcelByForm")]
        public ApiActionResult<ExcelModel> GetExcelByForm()
        {
            return SafeExecute<ExcelModel>((messages) =>
            {
                ExcelModel ret = new ExcelModel();
                var httpRequest = HttpContext.Request;
                var indv = httpRequest.Form["index"];
                var index = indv.Any() ? LibSysUtils.ToInt(indv[0]) : 0;
                var sheetv = httpRequest.Form["sheet"];
                var sheetx = indv.Any() ? LibSysUtils.ToInt(sheetv[0]) : 0;
                var indexs = httpRequest.Form["sheets"];
                var indexsd = indexs.Any() && !string.IsNullOrEmpty(indexs[0]) ? indexs[0].Split(',').ToDictionary(t => LibSysUtils.ToInt(t.Split(':')[0]), t => LibSysUtils.ToInt(t.Split(':')[1])) : null;
                foreach (var key in httpRequest.Form.Files)
                {
                    var postedFile = key;
                    string fileName = postedFile.FileName;
                    var pos = fileName.LastIndexOf(".");
                    var extendName = fileName.Substring(pos);
                    var inputStream = postedFile.OpenReadStream();
                    bool isSuppot = extendName == ".xls" || extendName == ".xlsx";
                    if (!isSuppot)
                    {
                        continue;
                    }

                    if (indexsd != null && indexsd.Any())
                    {
                        var s = ExcelToTableUtils.ReadExcelByOledb(fileName, inputStream, indexsd);
                        if (s != null)
                        {
                            for (var i = 0; i < s.Tables.Count; i++)
                            {
                                ExcelTableModel table = new ExcelTableModel();
                                AddTableToExcelModel(table, s.Tables[i]);
                                ret.Tables.Add(table);
                            }
                        }
                    }
                    else
                    {
                        var t = ExcelToTableUtils.ReadExcelTable(fileName, inputStream, index);
                        if (t != null)
                        {
                            ExcelTableModel table = new ExcelTableModel();
                            AddTableToExcelModel(table, t);
                            ret.Tables.Add(table);
                        }
                    }
                }

                return ret;
            });
        }

        [HttpPost, Route("GetExcelDataByForm")]
        public ApiActionResult<ExcelByDataModel> GetExcelDataByForm()
        {
            return SafeExecute<ExcelByDataModel>((messages) =>
            {
                ExcelByDataModel ret = null;
                var httpRequest = HttpContext.Request;
                var indv = httpRequest.Form["sheetindex"];
                var index = indv.Any() ? indv[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(t => LibSysUtils.ToInt(t)).ToList() : null;
                var sheetv = httpRequest.Form["sheetname"];
                var sheetx = sheetv.Any() ? sheetv[0].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList() : null;

                foreach (var key in httpRequest.Form.Files)
                {
                    var postedFile = key;
                    var inputStream = postedFile.OpenReadStream();
                    if (key.FileName.Contains(".csv"))
                    {
                        ret = ExcelToDataUtils.ExcelImportToExcelDataByCVS(inputStream);
                        ret.FileName = key.FileName;
                    }
                    else
                    {
                        ret = ExcelToDataUtils.ExcelImportToExcelData(inputStream, sheetx, index);
                        ret.FileName = key.FileName;
                    }
                }

                return ret;
            });
        }

        [HttpPost, Route("GetExcelByByte")]
        public ApiActionResult<ExcelModel> GetExcelByByte([FromBody] ExcelParam excelParam)
        {
            return SafeExecute<ExcelModel>((messages) =>
            {
                ExcelModel ret = new ExcelModel();
                if (string.IsNullOrEmpty(excelParam.FileName) || excelParam.Data == null || !excelParam.Data.Any())
                {
                    return ret;
                }

                var index = excelParam.Index;
                var sheetx = excelParam.Sheet;
                string fileName = excelParam.FileName;
                var pos = fileName.LastIndexOf(".");
                var extendName = fileName.Substring(pos);
                bool isSuppot = extendName == ".xls" || extendName == ".xlsx";
                if (!isSuppot)
                {
                    return ret;
                }

                using (MemoryStream inputStream = new MemoryStream(excelParam.Data))
                {
                    if (sheetx != 0)
                    {
                        var s = ExcelToTableUtils.ReadExcelByOledb(fileName, inputStream, null);
                        if (s != null)
                        {
                            for (var i = 0; i < s.Tables.Count; i++)
                            {
                                ExcelTableModel table = new ExcelTableModel();
                                AddTableToExcelModel(table, s.Tables[i]);
                                ret.Tables.Add(table);
                            }
                        }
                    }
                    else
                    {
                        var t = ExcelToTableUtils.ReadExcelTable(fileName, inputStream, index);
                        if (t != null)
                        {
                            ExcelTableModel table = new ExcelTableModel();
                            AddTableToExcelModel(table, t);
                            ret.Tables.Add(table);
                        }
                    }
                }

                return ret;
            });
        }

        [HttpPost, Route("ImportDayPlanExcel")]
        public ApiActionResult<List<ImportDayPlanModel>> ImportDayPlanExcel(IFormFile file)
        {
            return SafeExecute<List<ImportDayPlanModel>>((messages) =>
            {
                try
                {
                    var result = new List<ImportDayPlanModel>();
                    var dataTable = ExcelToTableUtils.ExcelImportToDataTable(file, 2, 0);
                    if (dataTable == null || dataTable.Rows.Count < 1)
                    {
                        return null;
                    }

                    var todayIndex = -1;
                    var tomorrowIndex = -1;
                    for (int i = 0; i < dataTable.Columns.Count; i++)
                    {
                        try
                        {
                            var dateTime = Convert.ToDateTime(dataTable.Columns[i].ColumnName);

                            if (dateTime.Date == DateTime.Now.Date)
                            {
                                todayIndex = i;
                            }

                            if (dateTime.Date == DateTime.Now.AddDays(1).Date)
                            {
                                tomorrowIndex = i;
                            }
                        }
                        catch
                        {
                            ////this.AddError("06900003", null);
                            ////return null;
                        }
                    }

                    foreach (DataRow row in dataTable.Rows)
                    {
                        if (todayIndex >= 0 && !string.IsNullOrEmpty(row[todayIndex].ToString()))
                        {
                            if (!string.IsNullOrEmpty(row["线别"].ToString()) && !string.IsNullOrEmpty(row["生产订单号"].ToString()) && !string.IsNullOrEmpty(row["物料ID"].ToString()))
                            {
                                var item = GetImportDayPlanModel(row, todayIndex, DateTime.Now);
                                if (item != null)
                                {
                                    result.Add(item);
                                }
                            }
                        }

                        if (tomorrowIndex >= 0 && !string.IsNullOrEmpty(row[tomorrowIndex].ToString()))
                        {
                            if (!string.IsNullOrEmpty(row["线别"].ToString()) && !string.IsNullOrEmpty(row["生产订单号"].ToString()) && !string.IsNullOrEmpty(row["物料ID"].ToString()))
                            {
                                var item = GetImportDayPlanModel(row, tomorrowIndex, DateTime.Now.AddDays(1));
                                if (item != null)
                                {
                                    result.Add(item);
                                }
                            }
                        }
                    }

                    return result;
                }
                catch
                {
                    this.AddError("06900004", null);
                    return null;
                }
            });
        }

        /// <summary>
        /// PQC导出---通用模板
        /// </summary>
        /// <param name="orderModel"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost, Route("ExportQCOrderExcel")]
        public ActionResult ExportQCOrderExcel([FromBody] QCOrderModel orderModel)
        {
            if (!orderModel.DetailModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault().QCOrderItemModels.Any())
            {
                return null;
            }

            var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            int rowNum = 0;
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            GenerateExcelHeader(workbook, sheet, rowNum, reportType, version.SamplingSchemeName);
            MergedRegion(sheet, 0, 0, 0, 7, workbook);
            rowNum++;
            GenerateSheetHeader(workbook, sheet, rowNum, orderModel);
            rowNum = rowNum + 3;
            AddBatchItemHeadRow(workbook, sheet, rowNum);
            MergedRegion(sheet, rowNum, rowNum, 1, 3, workbook);
            version.QCOrderItemModels.OrderBy(p => p.Index).ForEach(o =>
              {
                  rowNum++;
                  AddBatchItemDataRow(o, workbook, sheet, rowNum);
              });

            rowNum++;
            GenerateSheetFooter(workbook, sheet, rowNum, orderModel);
            for (int i = 0; i < 8; i++)
            {
                sheet.SetColumnWidth(i, 10 * 256);
            }

            sheet.SetColumnWidth(3, 16 * 256);

            if (orderModel.Type != 1)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.xlsx";
                    return File(ms.ToArray(), "application/octet-stream", name);
                }
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.pdf";
                return File(pdfService.Render(ms.ToArray()), "application/octet-stream", name);
            }
        }

        /// <summary>
        /// PQC导出--合宜模板
        /// </summary>
        /// <param name="orderModel"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost, Route("ExportQCOrderExcelX")]
        public ActionResult ExportQCOrderExcelX([FromBody] QCOrderModel orderModel)
        {
            if (!orderModel.DetailModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault().QCOrderItemModels.Any())
            {
                return null;
            }

            var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            int rowNum = 0;
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            GenerateExcelHeader(workbook, sheet, rowNum, reportType, version.SamplingSchemeName);
            MergedRegion(sheet, 0, 0, 0, 7, workbook);
            rowNum++;
            heyi.GenerateSheetHeader(workbook, sheet, rowNum, orderModel);
            rowNum = rowNum + 3;
            heyi.AddBatchItemHeadRow(workbook, sheet, rowNum);

            version.QCOrderItemModels.OrderBy(p => p.Index).ForEach(o =>
            {
                rowNum++;
                heyi.AddBatchItemDataRow(o, workbook, sheet, rowNum);
            });

            rowNum++;
            heyi.GenerateSheetFooter(workbook, sheet, rowNum, orderModel);
            for (int i = 0; i < 8; i++)
            {
                sheet.SetColumnWidth(i, 10 * 256);
            }

            sheet.SetColumnWidth(3, 16 * 256);

            if (orderModel.Type != 1)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.xlsx";
                    return File(ms.ToArray(), "application/octet-stream", name);
                }
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.pdf";
                return File(pdfService.Render(ms.ToArray()), "application/octet-stream", name);
            }
        }

        /// <summary>
        /// OQC导出
        /// </summary>
        /// <param name="orderModel"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost, Route("ExportOQCOrderExcel")]
        public async Task<ActionResult> ExportOQCOrderExcel([FromBody] QCOrderModel orderModel)
        {
            if (!orderModel.DetailModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault().QCOrderItemModels.Any())
            {
                return null;
            }

            var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            GenerateSheetSetColunmsWidth(sheet);
            int rowNum = 0;
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            GenerateExcelOQCHeader(workbook, sheet, rowNum, orderModel.OrgName);
            rowNum += 2;
            GenerateSheetOQCHeader(workbook, sheet, rowNum, orderModel);
            rowNum += 6;

            var groupItem = version.QCOrderItemModels.OrderBy(p => p.CategoryType).GroupBy(p => p.CategoryCode).ToList();

            foreach (var o in groupItem)
            {
                var items = version.QCOrderItemModels.Where(p => p.CategoryCode == o.Key).OrderBy(p => p.Index).ToList();
                var defects = version.SamplingSchemeDefects.Where(p => p.ItemCategoryCode == o.Key).ToList();
                if (o.FirstOrDefault().CategoryType == 0)
                {
                    AddBatchItemOQCQualitativeRow(workbook, sheet, ref rowNum, items, defects, o.FirstOrDefault().CategoryName);
                    rowNum++;
                }
                else if (o.FirstOrDefault().CategoryType == 1)
                {
                    rowNum = await AddBatchItemDataOQCRationRow(workbook, sheet, rowNum, items, defects, o.FirstOrDefault().CategoryName, orderModel.Type);
                    rowNum++;
                }
            }

            ////AddBatchItemOQCQualitativeRow(workbook, sheet, rowNum, orderModel, "123");

            ////rowNum += 8;
            ////AddBatchItemDataOQCRationRow(workbook, sheet, rowNum, orderModel, "123");

            ////rowNum += 13;
            GenerateSheetOQCFooter(workbook, sheet, rowNum, orderModel);

            ////rowNum += 2;
            ////var region = new NPOI.SS.Util.CellRangeAddress(0, rowNum - 1, 0, 0);
            ////sheet.AddMergedRegion(region);
            if (orderModel.Type != 1)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.xlsx";
                    return File(ms.ToArray(), "application/octet-stream", name);
                }
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.pdf";
                return File(pdfService.Render(ms.ToArray()), "application/octet-stream", name);
            }
        }

        /// <summary>
        /// OQC导出--合宜工厂
        /// </summary>
        /// <param name="orderModel"></param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost, Route("ExportOQCOrderExcelX")]
        public ActionResult ExportOQCOrderExcelX([FromBody] QCOrderModel orderModel)
        {
            if (!orderModel.DetailModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.Any())
            {
                return null;
            }

            if (!orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault().QCOrderItemModels.Any())
            {
                return null;
            }

            var version = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();
            heyi.GenerateSheetSetColunmsWidth(sheet);
            int rowNum = 0;
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            heyi.GenerateExcelOQCHeader(workbook, sheet, rowNum, orderModel.OrgName);
            rowNum += 2;
            heyi.GenerateSheetOQCHeader(workbook, sheet, rowNum, orderModel);
            rowNum += 6;

            var groupItem = version.QCOrderItemModels.OrderBy(p => p.Index).GroupBy(p => p.CategoryCode);
            var cateIndex = 0;
            groupItem.ForEach(o =>
            {
                var items = version.QCOrderItemModels.Where(p => p.CategoryCode == o.Key).OrderBy(p => p.Index).ToList();
                var defects = version.SamplingSchemeDefects.Where(p => p.ItemCategoryCode == o.Key).ToList();
                if (o.FirstOrDefault().CategoryType == 0)
                {
                    heyi.AddBatchItemOQCQualitativeRow(workbook, sheet, ref rowNum, items, defects, o.FirstOrDefault().CategoryName, cateIndex);
                    rowNum++;
                }
                else if (o.FirstOrDefault().CategoryType == 1)
                {
                    heyi.AddBatchItemDataOQCRationRow(workbook, sheet, ref rowNum, items, defects, o.FirstOrDefault().CategoryName, cateIndex);
                    rowNum++;
                }

                cateIndex++;
            });

            ////AddBatchItemOQCQualitativeRow(workbook, sheet, rowNum, orderModel, "123");

            ////rowNum += 8;
            ////AddBatchItemDataOQCRationRow(workbook, sheet, rowNum, orderModel, "123");

            ////rowNum += 13;
            heyi.GenerateSheetOQCFooter(workbook, sheet, rowNum, orderModel);

            ////rowNum += 2;
            ////var region = new NPOI.SS.Util.CellRangeAddress(0, rowNum - 1, 0, 0);
            ////sheet.AddMergedRegion(region);
            if (orderModel.Type != 1)
            {
                using (var ms = new MemoryStream())
                {
                    workbook.Write(ms, false);
                    var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.xlsx";
                    return File(ms.ToArray(), "application/octet-stream", name);
                }
            }

            using (var ms = new MemoryStream())
            {
                workbook.Write(ms, false);
                var name = $"{reportType}_{version.SamplingSchemeName}_{DateTime.Now}.pdf";
                return File(pdfService.Render(ms.ToArray()), "application/octet-stream", name);
            }
        }

        /// <summary>
        /// 料站表导入
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route(nameof(GetMaterialStationExcelData))]
        public ApiActionResult<ExcelModel> GetMaterialStationExcelData()
        {
            return SafeExecute<ExcelModel>((messages) =>
            {
                ExcelModel ret = new ExcelModel();
                var httpRequest = HttpContext.Request;
                var indv = httpRequest.Form["index"];
                var index = indv.Any() ? LibSysUtils.ToInt(indv[0]) : 0;
                var sheetv = httpRequest.Form["sheet"];
                var sheetx = indv.Any() ? LibSysUtils.ToInt(sheetv[0]) : 0;
                var indexs = httpRequest.Form["sheets"];
                var indexsd = indexs.Any() && !string.IsNullOrEmpty(indexs[0]) ? indexs[0].Split(',').ToDictionary(t => LibSysUtils.ToInt(t.Split(':')[0]), t => LibSysUtils.ToInt(t.Split(':')[1])) : null;
                foreach (var key in httpRequest.Form.Files)
                {
                    var postedFile = key;
                    string fileName = postedFile.FileName;
                    var pos = fileName.LastIndexOf(".");
                    var extendName = fileName.Substring(pos);
                    var inputStream = postedFile.OpenReadStream();
                    bool isSuppot = extendName == ".xls" || extendName == ".xlsx" || extendName == ".xlt";
                    if (!isSuppot)
                    {
                        continue;
                    }

                    if (indexsd != null && indexsd.Any())
                    {
                        var s = ExcelToTableUtils.ReadItemStationExcel(fileName, inputStream, indexsd);
                        if (s != null)
                        {
                            for (var i = 0; i < s.Tables.Count; i++)
                            {
                                ExcelTableModel table = new ExcelTableModel();
                                AddTableToExcelModel(table, s.Tables[i]);
                                ret.Tables.Add(table);
                            }
                        }
                    }
                    else
                    {
                        var t = ExcelToTableUtils.ReadExcelTable(fileName, inputStream, index);
                        if (t != null)
                        {
                            ExcelTableModel table = new ExcelTableModel();
                            AddTableToExcelModel(table, t);
                            ret.Tables.Add(table);
                        }
                    }
                }

                return ret;
            });
        }

        [HttpPost, Route("GetLineProByForm")]
        public ApiActionResult<ExcelModel> GetLineProByForm()
        {
            return SafeExecute<ExcelModel>((messages) =>
            {
                ExcelModel ret = new ExcelModel();
                var httpRequest = HttpContext.Request;
                var indv = httpRequest.Form["index"];
                var index = indv.Any() ? LibSysUtils.ToInt(indv[0]) : 0;
                var sheetv = httpRequest.Form["sheet"];
                var sheetx = indv.Any() ? LibSysUtils.ToInt(sheetv[0]) : 0;
                var indexs = httpRequest.Form["sheets"];
                var indexsd = indexs.Any() && !string.IsNullOrEmpty(indexs[0]) ? indexs[0].Split(',').ToDictionary(t => LibSysUtils.ToInt(t.Split(':')[0]), t => LibSysUtils.ToInt(t.Split(':')[1])) : null;
                foreach (var key in httpRequest.Form.Files)
                {
                    var postedFile = key;
                    string fileName = postedFile.FileName;
                    var pos = fileName.LastIndexOf(".");
                    var extendName = fileName.Substring(pos);
                    var inputStream = postedFile.OpenReadStream();
                    bool isSuppot = extendName == ".xls" || extendName == ".xlsx";
                    if (!isSuppot)
                    {
                        continue;
                    }

                    if (indexsd != null && indexsd.Any())
                    {
                        var s = ExcelToTableUtils.ReadExcelByOledb(fileName, inputStream, indexsd);
                        if (s != null)
                        {
                            for (var i = 0; i < s.Tables.Count; i++)
                            {
                                ExcelTableModel table = new ExcelTableModel();
                                AddTableToExcelModel(table, s.Tables[i]);
                                ret.Tables.Add(table);
                            }

                            for (var i = 0; i < s.Tables.Count; i++)
                            {
                                ExcelTableModel table = new ExcelTableModel();
                                AddTableToExcelModel(table, s.Tables[i]);
                                ret.Tables.Add(table);
                            }

                            int tableIndex = 0;

                            foreach (var table in ret.Tables)
                            {
                                tableIndex++;
                                var productIndex = 0;
                                var lineIndex = 0;
                                table.Head.Data.Add("产品线");

                                for (int i = 0; i < table.Head.Data.Count; i++)
                                {
                                    if (table.Head.Data[i] == "产品ID")
                                    {
                                        productIndex = i;
                                    }

                                    if (table.Head.Data[i] == "产线")
                                    {
                                        lineIndex = i;
                                    }
                                }

                                var removes = new List<int>();

                                for (int i = 0; i < table.Rows.Count; i++)
                                {
                                    if (table.Rows[i].Data[lineIndex] == string.Empty && table.Rows[i].Data[productIndex] == string.Empty)
                                    {
                                        removes.Add(i);
                                        table.Rows[i].Data[lineIndex] = table.Rows[i - 1].Data[lineIndex];
                                        table.Rows[i].Data[productIndex] = table.Rows[i - 1].Data[productIndex];
                                    }

                                    table.Rows[i].Data.Add(table.Rows[i].Data[lineIndex] + table.Rows[i].Data[productIndex]);
                                }

                                if (tableIndex == 1)
                                {
                                    foreach (var remove in removes)
                                    {
                                        table.Rows.RemoveAt(remove);
                                    }
                                }
                            }
                        }
                    }
                }

                return ret;
            });
        }

        private void MergedRegion(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, IWorkbook workbook)
        {
            var region = new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.AddMergedRegion(region);
            RegionUtil.SetBorderLeft(1, region, sheet);
            RegionUtil.SetBorderRight(1, region, sheet);
            RegionUtil.SetBorderTop(1, region, sheet);
            RegionUtil.SetBorderBottom(1, region, sheet);
        }

        #region PQC导出
        private void AddBatchItemHeadRow(IWorkbook workbook, ISheet sheet, int rowNum)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderStyle(workbook);
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验类型");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("判定结果");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("不良代码");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("图片");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("样本");

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        private void AddBatchItemDataRow(QCOrderItemModel batchItem, IWorkbook workbook, ISheet sheet, int rowNum)
        {
            var column = 0;
            IRow row = sheet.CreateRow(rowNum);
            var badRemark = string.Empty;
            batchItem.QCOrderItemBadModels.ForEach(o =>
            {
                badRemark += string.Format("{0}-{1}-{2};", o.BadName, Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel ?? 0), o.BadQty);
            });

            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.CategoryName);
            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.ItemName);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            row.CreateCell(column++, CellType.String).SetCellValue(batchItem.Result == 0 ? "NA" : batchItem.Result == 1 ? "OK" : "NG");
            row.CreateCell(column++, CellType.String).SetCellValue(badRemark);
            row.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            var sampleValue = string.Empty;
            var height = Convert.ToInt16(((batchItem.ItemName.Length / 17) + 1) * 280);
            if (batchItem.QCOrderItemSamplingModels != null)
            {
                var tempData = batchItem.QCOrderItemSamplingModels.OrderByDescending(t => t.Sample).Select(item => item.Sample.ToString()).ToArray();
                var sampleHeight = 0;
                for (int i = 0; i < tempData.Length; i++)
                {
                    tempData[i] = "(" + (i + 1).ToString() + ")" + tempData[i];
                    sampleHeight += Convert.ToInt16((((tempData[i].Length + 3) / 7) + 1) * 280);
                }

                sampleValue = string.Join(";\n", tempData);
                if (!string.IsNullOrEmpty(batchItem.Answer))
                {
                    sampleHeight += Convert.ToInt16(((batchItem.Answer.Length / 7) + 1) * 280);
                    sampleValue = batchItem.Answer + ";\n" + sampleValue;
                }

                height = height < sampleHeight ? Convert.ToInt16(sampleHeight) : height;
            }

            row.Height = height;
            row.CreateCell(column++, CellType.String).SetCellValue(sampleValue);
            MergedRegion(sheet, rowNum, rowNum, 1, 3, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, row);
        }

        private void GenerateSheetFooter(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ///ICellStyle boldStyle = GetExcelHeaderStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("题目汇总");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.QCOrderItemModels.Count.ToString());
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("检验员");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.CheckUserName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("版本号");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.Versions);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            column = 0;
            headerRow = sheet.CreateRow(rowNum + 1);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("总结");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("总关键工序题目数（除去NA):");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.QCOrderItemModels.Where(p => p.Result != 0).Count().ToString());
            headerRow.CreateCell(column++, CellType.String).SetCellValue("不良汇总（NG):");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.QCOrderItemModels.Where(p => p.Result == 2).Count().ToString());
            headerRow.CreateCell(column++, CellType.String).SetCellValue("表单编号");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.TableCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Empty);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
        }

        private void GenerateSheetHeader(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.OrderByDescending(p => p.Versions).FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("销售订单");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.SaleCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("销售订单行项目");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.SaleRow);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产工单");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(detail.WorkCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("客户简称");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.CustomerName);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            column = 0;
            headerRow = sheet.CreateRow(rowNum + 1);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.IsShowNakedLamp ? "裸灯ID" : "物料ID");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.ProductCode);
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.IsShowNakedLamp ? "裸灯描述" : "物料描述");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.ProductName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产工厂");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(orderModel.OrgName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产课别");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(string.Format("{0}/{1}/{2}", orderModel.OrgName, orderModel.DepName, orderModel.SetionName));
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            column = 0;
            headerRow = sheet.CreateRow(rowNum + 2);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("生产线别");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(detail.LineName);
            headerRow.CreateCell(column++, CellType.String).SetCellValue("班次");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("1");
            headerRow.CreateCell(column++, CellType.String).SetCellValue("报表提交时间");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.CreateTime.Value.ToString("yyyy-MM-dd"));
            headerRow.CreateCell(column++, CellType.String).SetCellValue(reportType + "确认结果");
            headerRow.CreateCell(column++, CellType.String).SetCellValue(versionModel.DecisionPolicy == 3001 ? "OK" : "NG");
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
        }

        private void GenerateExcelHeader(IWorkbook workbook, ISheet sheet, int rowNum, string reportType, string samplingSchemeName)
        {
            ///MergedRegion(sheet, 0, 0, 0, 8, workbook);
            ///sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderStyle(workbook);

            var column = 0;
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(column, CellType.String).SetCellValue($"{reportType}_{samplingSchemeName}");

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }
        #endregion

        #region OQC导出
        /// <summary>
        /// 定性模板
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        private void AddBatchItemOQCQualitativeRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName)
        {
            var rowCount = AddBatchItemDataOQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName);
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(9, CellType.String).SetCellValue("RE");
            headerRow.CreateCell(11, CellType.String).SetCellValue("抽样数");
            headerRow.CreateCell(15, CellType.String).SetCellValue("不良数");
            headerRow.CreateCell(20, CellType.String).SetCellValue("异常描述");
            headerRow.CreateCell(30, CellType.String).SetCellValue("缺陷等级");
            headerRow.CreateCell(33, CellType.String).SetCellValue("判定");
            headerRow.CreateCell(37, CellType.String).SetCellValue("附件");
            OQCQualitativeMergedRegion(workbook, sheet, rowNum);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var badRemark = string.Empty;
                item.QCOrderItemBadModels.ForEach(o =>
                {
                    badRemark += string.Format("{0}-{1}-{2};", o.BadName, Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel ?? 0), o.BadQty);
                });

                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(9, CellType.String).SetCellValue(item.ItemRe);
                headerRow.CreateCell(11, CellType.String).SetCellValue(item.SampleQty.ToString("0"));
                headerRow.CreateCell(15, CellType.String).SetCellValue(item.QCOrderItemBadModels.Sum(p => p.BadQty).ToString("0"));
                headerRow.CreateCell(20, CellType.String).SetCellValue(badRemark);
                headerRow.CreateCell(30, CellType.String).SetCellValue(Enum.GetName(typeof(DefectLevelEnum), item.DefectLevel));
                headerRow.CreateCell(33, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                headerRow.CreateCell(37, CellType.String).SetCellValue(string.Empty);
                OQCQualitativeMergedRegion(workbook, sheet, rowNum);
                headerRow.Height = Convert.ToInt16(((item.ItemName.Length / 12) + 1) * 280);
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);
        }

        private void OQCQualitativeMergedRegion(IWorkbook workbook, ISheet sheet, int rowNum)
        {
            MergedRegion(sheet, rowNum, rowNum, 2, 8, workbook);
            MergedRegion(sheet, rowNum, rowNum, 9, 10, workbook);
            MergedRegion(sheet, rowNum, rowNum, 11, 14, workbook);
            MergedRegion(sheet, rowNum, rowNum, 15, 19, workbook);
            MergedRegion(sheet, rowNum, rowNum, 20, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 32, workbook);
            MergedRegion(sheet, rowNum, rowNum, 33, 36, workbook);
            MergedRegion(sheet, rowNum, rowNum, 37, 38, workbook);
        }

        private int AddBatchItemDataOQCHeaderRow(IWorkbook workbook, ISheet sheet, ref int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            var rowCount = 0;
            var defectStr = string.Empty;
            var hasDefect = false;
            var inspectionLevel = string.Empty;
            if (defects != null && defects.Any())
            {
                var defect = defects.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                defects.ForEach(o =>
                {
                    defectStr += string.Format("{0}类:(AQL:{1} Ac={2} Re={3}) ; ", Enum.GetName(typeof(DefectLevelEnum), o.DefectLevel), o.AQL, o.ItemAc, o.ItemRe);
                });

                inspectionLevel = string.Format("检验水平:{0} 抽样数:{1}PS", defect.StdName + defect.InspectionLevelCode, defect.SampleQty.HasValue ? defect.SampleQty.Value.ToString("0") : "0");
                hasDefect = true;
            }
            else
            {
                var item = items.OrderByDescending(p => p.SampleQty).FirstOrDefault();
                inspectionLevel = string.Format("抽样数量:{1} Ac={2} Re={3} 不合格数:{0} ; ", items.Where(p => p.Result == 2).Count().ToString("0"), item.SampleQty.ToString("0"), item.ItemAc, item.ItemRe);
            }

            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue(categoryName);

            headerRow.CreateCell(1, CellType.String).SetCellValue(inspectionLevel);
            headerRow.CreateCell(32, CellType.String).SetCellValue(string.Format("结论：{0}", items.Any(p => p.Result == 2) ? "NG" : "OK"));
            MergedRegion(sheet, rowNum, rowNum, 1, 31, workbook);
            MergedRegion(sheet, rowNum, rowNum, 32, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            headerRow.Cells[1].CellStyle.Alignment = HorizontalAlignment.Justify;

            if (hasDefect)
            {
                ///大类缺陷等级
                rowCount++;
                rowNum++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.CreateCell(1, CellType.String).SetCellValue(defectStr);
                MergedRegion(sheet, rowNum, rowNum, 1, 38, workbook);
                headerRow.Cells.ForEach(cell =>
                {
                    cell.CellStyle = boldStyle;
                });
            }

            rowNum++;
            rowCount++;
            return rowCount;
        }

        /// <summary>
        /// 定量模板
        /// </summary>
        /// <param name="batchItem"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        private async Task<int> AddBatchItemDataOQCRationRow(IWorkbook workbook, ISheet sheet, int rowNum, List<QCOrderItemModel> items, List<QCOrderCategoryDefectModel> defects, string categoryName, int type)
        {
            var rowCount = AddBatchItemDataOQCHeaderRow(workbook, sheet, ref rowNum, items, defects, categoryName);
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(1, CellType.String).SetCellValue("NO");
            headerRow.CreateCell(2, CellType.String).SetCellValue("检验项目");
            headerRow.CreateCell(7, CellType.String).SetCellValue("范围值");
            headerRow.CreateCell(13, CellType.String).SetCellValue("测试结果(每个尺寸项目随机记录实测值)");
            headerRow.CreateCell(36, CellType.String).SetCellValue("附件");
            MergedRegion(sheet, rowNum, rowNum, 2, 6, workbook);
            MergedRegion(sheet, rowNum, rowNum, 7, 12, workbook);
            MergedRegion(sheet, rowNum, rowNum, 13, 35, workbook);
            MergedRegion(sheet, rowNum, rowNum, 36, 38, workbook);
            ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();

            for (var i = 1; i <= items.Count; i++)
            {
                rowNum++;
                rowCount++;
                var item = items[i - 1];
                headerRow = sheet.CreateRow(rowNum);
                var rowsCount = Convert.ToInt16((item.ItemName.Length / 12) + 1);
                var height = Convert.ToInt16(((rowsCount / 2) * 280) + ((rowsCount % 2) * 140));
                height = height > 280 ? height : Convert.ToInt16(280);
                headerRow.Height = height;
                headerRow.CreateCell(1, CellType.String).SetCellValue(i.ToString());
                headerRow.CreateCell(2, CellType.String).SetCellValue(item.ItemName);
                headerRow.CreateCell(7, CellType.String).SetCellValue(item.LowerValue + "/" + item.StandardValue + "/" + item.UpperValue);
                int index = 13;

                ///样本值序号
                for (var j = 1; j <= 10; j++)
                {
                    headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(j.ToString());
                }

                for (var j = 1; j <= 10; j++)
                {
                    MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                }

                headerRow.CreateCell(33, CellType.String).SetCellValue(Enum.GetName(typeof(QcCheckResultEnum), item.Result));
                headerRow.CreateCell(36, CellType.String).SetCellValue(string.Empty);

                ///样本值
                rowNum++;
                rowCount++;
                headerRow = sheet.CreateRow(rowNum);
                headerRow.Height = height;
                for (var j = 1; j <= 10; j++)
                {
                    item.QCOrderItemSamplingModels = item.QCOrderItemSamplingModels.OrderBy(p => p.Index).ToList();
                    if (item.QCOrderItemSamplingModels != null && item.QCOrderItemSamplingModels.Count >= j)
                    {
                        headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(item.QCOrderItemSamplingModels[j - 1].Sample.ToString());
                    }
                    else
                    {
                        headerRow.CreateCell(index + ((j - 1) * 2), CellType.String).SetCellValue(string.Empty);
                    }
                }

                for (var j = 1; j <= 10; j++)
                {
                    MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                }

                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);

                if (type == 2)
                {
                    rowNum++;
                    rowCount++;
                    headerRow = sheet.CreateRow(rowNum);
                    headerRow.Height = 50 * 30;

                    if (item.SamplingPhotosFiles != null)
                    {
                        item.Photos = JsonConvert.DeserializeObject<List<UploadImgModel>>(item.SamplingPhotosFiles);
                        var fileIds = item.Photos.Select(t => t.FileId).ToList();
                        var files = await fileRepository.GetFilesByIdsAsync(fileIds);

                        ///图片
                        for (var j = 1; j <= item.Photos.Count && j <= 10; j++)
                        {
                            ICell cell = headerRow.CreateCell(index + ((j - 1) * 2), CellType.String);
                            var url = this.configuration["ServerUrl"] + "api/File/Download/DownloadFile?id=" + item.Photos[j - 1].FileId;
                            sheet.SetColumnWidth(index + ((j - 1) * 2), 15 * 256);
                            AddCellPicture(workbook, drawing, url, rowNum, rowNum + 1, index + ((j - 1) * 2), index + (j * 2) - 1);
                        }
                    }

                    for (var j = 1; j <= 10; j++)
                    {
                        MergedRegion(sheet, rowNum, rowNum, index + ((j - 1) * 2), index + (j * 2) - 1, workbook);
                    }
                }

                var preRowNum = rowNum - 1;

                if (type == 2)
                {
                    preRowNum = rowNum - 2;
                }

                MergedRegion(sheet, preRowNum, rowNum, 1, 1, workbook);
                MergedRegion(sheet, preRowNum, rowNum, 2, 6, workbook);
                MergedRegion(sheet, preRowNum, rowNum, 7, 12, workbook);
                MergedRegion(sheet, preRowNum, rowNum, 33, 35, workbook);
                MergedRegion(sheet, preRowNum, rowNum, 36, 38, workbook);
                ExcelStyleUtils.SetRowWrapText(workbook, headerRow);
            }

            MergedRegion(sheet, rowNum - rowCount, rowNum, 0, 0, workbook);

            return rowNum;
        }

        private void AddCellPicture(IWorkbook workbook, XSSFDrawing drawing, string fileUrl, int firstRow, int lastRow, int firstCol, int lastCol)
        {
            if (fileUrl == null || fileUrl == string.Empty || fileUrl.IndexOf("/") == -1 || fileUrl.IndexOf(".") == -1)
            {
                return;
            }

            if (!string.IsNullOrEmpty(fileUrl))
            {
                byte[] bytes = UrlToByte(fileUrl);
                int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, firstCol, firstRow, lastCol, lastRow);
                drawing.CreatePicture(anchor, pictureIdx);
            }
        }

        private byte[] UrlToByte(string filePath)
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(filePath);
            byte[] bytes;

            using (Stream stream = request.GetResponse().GetResponseStream())
            {
                using (MemoryStream mstream = new MemoryStream())
                {
                    int count = 0;
                    byte[] buffer = new byte[1024];
                    int readNum = 0;

                    while ((readNum = stream.Read(buffer, 0, 1024)) > 0)
                    {
                        count = count + readNum;
                        mstream.Write(buffer, 0, readNum);
                    }

                    mstream.Position = 0;

                    using (BinaryReader br = new BinaryReader(mstream))
                    {
                        bytes = br.ReadBytes(count);
                    }
                }
            }

            return bytes;
        }

        /// <summary>
        /// OQC表尾
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        private void GenerateSheetOQCFooter(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            IRow headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 3 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("备注(客户特殊要求)");
            headerRow.CreateCell(4, CellType.String).SetCellValue(versionModel.Remark);
            MergedRegion(sheet, rowNum, rowNum, 0, 3, workbook);
            MergedRegion(sheet, rowNum, rowNum, 4, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("审核");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.ExamineSealCode);
            headerRow.CreateCell(6, CellType.String).SetCellValue("检验员");
            headerRow.CreateCell(14, CellType.String).SetCellValue(versionModel.CheckUserName);
            headerRow.CreateCell(18, CellType.String).SetCellValue("表单编号");
            headerRow.CreateCell(22, CellType.String).SetCellValue(versionModel.TableCode);
            headerRow.CreateCell(28, CellType.String).SetCellValue("保存年限");
            headerRow.CreateCell(32, CellType.String).SetCellValue(versionModel.ShelfLife + "年");
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 13, workbook);
            MergedRegion(sheet, rowNum, rowNum, 14, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 21, workbook);
            MergedRegion(sheet, rowNum, rowNum, 22, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 31, workbook);
            MergedRegion(sheet, rowNum, rowNum, 32, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// 表头明细
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="orderModel"></param>
        private void GenerateSheetOQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, QCOrderModel orderModel)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelOQCStyle(workbook);
            string reportType = ((SectiongGroupEnum)orderModel.SectionGroup).ToString();
            var versionModel = orderModel.VersionModels.FirstOrDefault();
            var detail = orderModel.DetailModels.FirstOrDefault();
            IRow headerRow = sheet.CreateRow(rowNum);
            var saleCode = string.Empty;
            if (!string.IsNullOrEmpty(orderModel.SaleCode))
            {
                saleCode = orderModel.SaleCode;
                if (!string.IsNullOrEmpty(orderModel.SaleRow))
                {
                    saleCode += orderModel.SaleRow.Replace("000", "-");
                }
            }

            headerRow.CreateCell(0, CellType.String).SetCellValue("MES检验单号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.Code);
            headerRow.CreateCell(6, CellType.String).SetCellValue("销售订单号");
            headerRow.CreateCell(12, CellType.String).SetCellValue(saleCode);
            headerRow.CreateCell(18, CellType.String).SetCellValue("抽样方法");
            headerRow.CreateCell(23, CellType.String).SetCellValue(versionModel.SamplingSchemeLevel == 0 ? "正常" : versionModel.SamplingSchemeLevel == 1 ? "放宽" : "加严");
            headerRow.CreateCell(34, CellType.String).SetCellValue("判定结论");
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 33, workbook);
            MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("检验日期");
            headerRow.CreateCell(3, CellType.String).SetCellValue(versionModel.CreateTime.HasValue ? versionModel.CreateTime.Value.ToString("yyyy-MM-dd") : string.Empty);
            headerRow.CreateCell(6, CellType.String).SetCellValue("生产订单号");
            headerRow.CreateCell(12, CellType.String).SetCellValue(detail.WorkCode);
            headerRow.CreateCell(18, CellType.String).SetCellValue("ID码");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.ProductCode);
            headerRow.CreateCell(28, CellType.String).SetCellValue("客户简称");
            headerRow.CreateCell(30, CellType.String).SetCellValue(orderModel.CustomerName);
            headerRow.CreateCell(34, CellType.String).SetCellValue(Enum.GetName(typeof(DecisionPolicyEnum), versionModel.DecisionPolicy));
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            if (orderModel.Type == 1)
            {
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }

            ////MergedRegion(sheet, rowNum, rowNum, 35, 39, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            var samplingQty = versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).HasValue && versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).Value > 0 ? versionModel.SamplingSchemeDefects.Max(p => p.SampleQty).Value : versionModel.QCOrderItemModels.Max(p => p.SampleQty);
            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("流水号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(string.Join(";", orderModel.DetailModels.Select(p => p.ExtCode)));
            headerRow.CreateCell(28, CellType.String).SetCellValue("抽样数量");
            headerRow.CreateCell(30, CellType.String).SetCellValue(samplingQty.ToString("0"));
            ///headerRow.CreateCell(35, CellType.String).SetCellValue("A023");
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }

            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.Height = 2 * 256;
            headerRow.CreateCell(0, CellType.String).SetCellValue("工单总数");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.WorkBomProductQty.Value.ToString("0"));
            headerRow.CreateCell(6, CellType.String).SetCellValue("检验批数");
            headerRow.CreateCell(12, CellType.String).SetCellValue(orderModel.DetailModels.Sum(p => p.BomProductQty) == 0 ? orderModel.DetailModels.Sum(p => p.SendQty).ToString("0") : orderModel.DetailModels.Sum(p => p.BomProductQty).ToString("0"));
            headerRow.CreateCell(18, CellType.String).SetCellValue("生产部门");
            headerRow.CreateCell(23, CellType.String).SetCellValue(orderModel.DepName);
            headerRow.CreateCell(28, CellType.String).SetCellValue("生产线别");
            headerRow.CreateCell(30, CellType.String).SetCellValue(detail.LineName);
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 17, workbook);
            MergedRegion(sheet, rowNum, rowNum, 18, 22, workbook);
            MergedRegion(sheet, rowNum, rowNum, 23, 27, workbook);
            MergedRegion(sheet, rowNum, rowNum, 28, 29, workbook);
            MergedRegion(sheet, rowNum, rowNum, 30, 33, workbook);
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("SAP检验批号");
            headerRow.CreateCell(3, CellType.String).SetCellValue(orderModel.SapCode);
            headerRow.CreateCell(6, CellType.String).SetCellValue("规格型号");
            headerRow.CreateCell(12, CellType.String).SetCellValue(orderModel.ProductName);
            ///headerRow.CreateCell(31, CellType.String).SetCellValue("1000258233");
            ///
            MergedRegion(sheet, rowNum, rowNum, 0, 2, workbook);
            MergedRegion(sheet, rowNum, rowNum, 3, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 11, workbook);
            MergedRegion(sheet, rowNum, rowNum, 12, 33, workbook);
            if (orderModel.Type == 1)
            {
                headerRow.CreateCell(34, CellType.String).SetCellValue(string.Empty);
                MergedRegion(sheet, rowNum, rowNum, 34, 38, workbook);
            }
            else
            {
                MergedRegion(sheet, 3, rowNum, 34, 38, workbook);
            }

            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });

            rowNum++;
            headerRow = sheet.CreateRow(rowNum);
            headerRow.CreateCell(0, CellType.String).SetCellValue("检验标准来源");
            headerRow.CreateCell(6, CellType.String).SetCellValue(versionModel.SamplingSchemeName);

            MergedRegion(sheet, rowNum, rowNum, 0, 5, workbook);
            MergedRegion(sheet, rowNum, rowNum, 6, 38, workbook);
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// 标题
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="rowNum"></param>
        /// <param name="reportType"></param>
        /// <param name="samplingSchemeName"></param>
        private void GenerateExcelOQCHeader(IWorkbook workbook, ISheet sheet, int rowNum, string orgName)
        {
            ICellStyle boldStyle = ExcelStyleUtils.GetExcelHeaderOQCStyle(workbook);
            IRow headerRow = sheet.CreateRow(rowNum);
            if (string.IsNullOrEmpty(orgName))
            {
                headerRow.CreateCell(0, CellType.String).SetCellValue($"漳州立达信光电子科技有限公司");
            }
            else
            {
                headerRow.CreateCell(0, CellType.String).SetCellValue(orgName);
            }

            MergedRegion(sheet, 0, 0, 0, 38, workbook);
            headerRow.Height = 50 * 10;
            headerRow.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
            rowNum++;
            IRow headerRow1 = sheet.CreateRow(rowNum);

            headerRow1.CreateCell(0, CellType.String).SetCellValue($"OQC-出货检验报告");
            MergedRegion(sheet, 1, 1, 0, 38, workbook);
            headerRow1.Height = 50 * 10;
            headerRow1.Cells.ForEach(cell =>
            {
                cell.CellStyle = boldStyle;
            });
        }

        /// <summary>
        /// OQC设置列宽
        /// </summary>
        /// <param name="sheet"></param>
        private void GenerateSheetSetColunmsWidth(ISheet sheet)
        {
            sheet.SetColumnWidth(0, 2 * 350);
            sheet.SetColumnWidth(1, 3 * 300);
            sheet.SetColumnWidth(2, 5 * 350);
            sheet.SetColumnWidth(3, 4 * 300);
            sheet.SetColumnWidth(4, 5 * 300);
            sheet.SetColumnWidth(5, 3 * 300);
            sheet.SetColumnWidth(6, 5 * 300);
            sheet.SetColumnWidth(7, 2 * 350);
            sheet.SetColumnWidth(8, 1 * 350);
            sheet.SetColumnWidth(9, 2 * 350);
            sheet.SetColumnWidth(10, 1 * 256);

            sheet.SetColumnWidth(11, 1 * 256);
            sheet.SetColumnWidth(12, 2 * 350);
            sheet.SetColumnWidth(13, 2 * 350);

            for (int i = 14; i < 38; i++)
            {
                sheet.SetColumnWidth(i, 3 * 300);
            }

            sheet.SetColumnWidth(38, 16 * 300);
        }
        #endregion

        private ImportDayPlanModel GetImportDayPlanModel(DataRow row, int index, DateTime date)
        {
            var item = new ImportDayPlanModel();
            item.LineName = row["线别"] == null ? string.Empty : row["线别"].ToString();
            item.PdtCode = row["生产订单号"] == null ? string.Empty : row["生产订单号"].ToString();
            item.WorkCode = row["工单号"] == null ? string.Empty : row["工单号"].ToString();
            item.ProductCode = row["物料ID"] == null ? string.Empty : row["物料ID"].ToString();
            item.ProductName = row["物料描述"] == null ? string.Empty : row["物料描述"].ToString();
            item.CompleteQty = row["已完总装"] == null || string.IsNullOrEmpty(row["已完总装"].ToString()) ? 0 : Convert.ToDecimal(row["已完总装"].ToString());
            item.Date = date;
            item.Qty = Convert.ToInt32(row[index] == null ? string.Empty : row[index].ToString());
            return item;
        }

        private void AddTableToExcelModel(ExcelTableModel ret, DataTable table)
        {
            if (ret.Head != null)
            {
                return;
            }

            var head = new ExcelHeadModel();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                head.Data.Add(table.Columns[i].ColumnName.Trim());
            }

            ret.Head = head;
            for (int j = 0; j < table.Rows.Count; j++)
            {
                var row = new ExcelRowModel();
                var r = table.Rows[j];
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    var col = table.Columns[i];
                    var v = r[col]?.ToString().Trim();
                    row.Data.Add(v ?? string.Empty);
                }

                ret.Rows.Add(row);
            }
        }
    }
}
